package com.appspot.showup_app.showup;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.appspot.showup_app.showup.model.ShowUpAppointmentMessage;
import com.appspot.showup_app.showup.model.ShowUpBusinessMessage;
import com.appspot.showup_app.showup.model.ShowUpCustomerMessage;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.SimpleTimeZone;


/**
 * Created by YOTAM on 11/27/2014.
 */
public class DatabaseHelper extends SQLiteOpenHelper {

    // If you change the database schema, you must increment the database version.
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "ShowUp.db";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public void onCreate(SQLiteDatabase db) {
        String createAppointmentTable =
                "CREATE TABLE appointment (" +
                        "id INTEGER PRIMARY KEY," +
                        "customer_id INTEGER," +
                        "title TEXT," +
                        "start_time INTEGER," +
                        "timezone TEXT," +
                        "duration INTEGER," +
                        "confirmation_status INTEGER," +
                        "confirmation_time INTEGER," +
                        "notification_sent NUMERIC," +
                        "notification_time INTEGER," +
                        "notes TEXT," +
                        "repeated INTEGER," +
                        "deleted NUMERIC," +
                        "remote_id TEXT," +
                        "short_url TEXT," +
                        "last_updated INTEGER" +
                        " )";

        db.execSQL(createAppointmentTable);

        String createCustomerTable =
                "CREATE TABLE customer (" +
                        "id INTEGER PRIMARY KEY," +
                        "name TEXT," +
                        "phone_number TEXT," +
                        "deleted NUMERIC," +
                        "email TEXT," +
                        "address TEXT," +
                        "last_updated INTEGER" +
                        " )";

        db.execSQL(createCustomerTable);
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // This database is only a cache for online data, so its upgrade policy is
        // to simply to discard the data and start over
        //db.execSQL(SQL_DELETE_ENTRIES);
        //onCreate(db);
    }

    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //onUpgrade(db, oldVersion, newVersion);
    }

    public static void addAppointment(SQLiteDatabase db, ShowUpAppointmentMessage apt) {
        ContentValues values = new ContentValues();
        values.put("id", apt.getId());
        values.put("customer_id", apt.getCustomer().getId());
        values.put("title", apt.getTitle());
        values.put("start_time", apt.getStartTime());
        values.put("timezone", "");
        values.put("duration", apt.getDuration());
        values.put("confirmation_status", apt.getConfirmationStatus());
        values.put("confirmation_time", 0);
        values.put("notification_time", 0);
        values.put("notes", apt.getNotes());
        values.put("repeated", apt.getRepeated());
        values.put("deleted", apt.getDeleted());
        values.put("remote_id", 0);
        values.put("short_url", "");
        values.put("last_updated", apt.getLastUpdated());

        db.replace("appointment", "null", values);
    }

    public static void addEditCustomer(SQLiteDatabase db, ShowUpCustomerMessage cust) {
        ContentValues values = new ContentValues();
        values.put("id", cust.getId());
        values.put("address", cust.getAddress());
        values.put("phone_number", cust.getPhoneNumber());
        values.put("email", cust.getEmail());
        values.put("name", cust.getName());
        values.put("deleted", cust.getDeleted());
        values.put("last_updated", cust.getLastUpdated());

        db.replace("customer", "null", values);
    }

    public static List<ShowUpAppointmentMessage> getAppointments(SQLiteDatabase db) {
        List<ShowUpAppointmentMessage> appointments = new ArrayList<ShowUpAppointmentMessage>();
        Cursor c = db.rawQuery("SELECT *, a.id as a_id, a.last_updated as a_last_updated, c.id as c_id, c.last_updated as c_last_updated" +
                " FROM appointment a INNER JOIN customer c" +
                " on a.customer_id = c.id ORDER BY start_time ASC", null);

        c.moveToFirst();
        if (c.getCount() > 0)
        {
            do {
                ShowUpAppointmentMessage appointmentMessage = getShowUpAppointmentMessage(c);

                //repeated appointments
                if (appointmentMessage.getRepeated()!=0){
                    Calendar cal = Calendar.getInstance(SimpleTimeZone.getTimeZone("Asia/Jerusalem"));
                    cal.setTime(DateTimeUtil.fromTimestamp(appointmentMessage.getStartTime()));
                    int day_of_month=cal.get(Calendar.DAY_OF_MONTH);
                    if (appointmentMessage.getRepeated()==1) {
                        for(int i=1; i<365; i++) {
                            cal.add(Calendar.DAY_OF_MONTH, 1);
                            ShowUpAppointmentMessage newAppointmentMessage= appointmentMessage.clone();
                            newAppointmentMessage.setStartTime((DateTimeUtil.getTimestamp(cal.getTime())));
                            appointments.add(newAppointmentMessage);
                        }
                    }
                    else if (appointmentMessage.getRepeated()==2) {
                        for(int i=1; i<52; i++) {
                            cal.add(Calendar.DAY_OF_MONTH, 7);
                            ShowUpAppointmentMessage newAppointmentMessage= appointmentMessage.clone();
                            newAppointmentMessage.setStartTime((DateTimeUtil.getTimestamp(cal.getTime())));
                            appointments.add(newAppointmentMessage);
                        }
                    }
                    else if (appointmentMessage.getRepeated()==3) {
                        for(int i=1; i<12; i++) {
                            cal.add(Calendar.MONTH, 1);
                            if(cal.get(Calendar.DAY_OF_MONTH) != day_of_month)
                                continue;
                            ShowUpAppointmentMessage newAppointmentMessage= appointmentMessage.clone();
                            newAppointmentMessage.setStartTime((DateTimeUtil.getTimestamp(cal.getTime())));
                            appointments.add(newAppointmentMessage);
                        }
                    }
                }

                appointments.add(appointmentMessage);

            }
            while(c.moveToNext());
        }
        c.close();

        Collections.sort(appointments);

        return appointments;
    }

    public static List<ShowUpCustomerMessage> getCustomers(SQLiteDatabase db) {
        List<ShowUpCustomerMessage> customers = new ArrayList<ShowUpCustomerMessage>();
        Cursor c = db.rawQuery("SELECT * FROM customer ORDER BY name ASC", null);

        c.moveToFirst();
        if (c.getCount() > 0)
        {
            do {
                ShowUpCustomerMessage customerMessage = getShowUpCustomerMessage(c, false);

                customers.add(customerMessage);

            }
            while(c.moveToNext());
        }
        c.close();

        return customers;
    }

    private static ShowUpAppointmentMessage getShowUpAppointmentMessage(Cursor c) {

        ShowUpAppointmentMessage appointmentMessage = new ShowUpAppointmentMessage();
        appointmentMessage.setId(c.getString(c.getColumnIndex("a_id")));
        appointmentMessage.setCustomer(getShowUpCustomerMessage(c, true));
        appointmentMessage.setTitle(c.getString(c.getColumnIndex("title")));
        appointmentMessage.setStartTime(c.getLong(c.getColumnIndex("start_time")));
        appointmentMessage.setTimezone(c.getString(c.getColumnIndex("timezone")));
        appointmentMessage.setDuration(c.getLong(c.getColumnIndex("duration")));
        appointmentMessage.setConfirmationStatus(c.getLong(c.getColumnIndex("confirmation_status")));
        appointmentMessage.setConfirmationTime(c.getLong(c.getColumnIndex("confirmation_time")));
        appointmentMessage.setNotificationTime(c.getLong(c.getColumnIndex("notification_time")));
        appointmentMessage.setNotes(c.getString(c.getColumnIndex("notes")));
        appointmentMessage.setRepeated(c.getLong(c.getColumnIndex("repeated")));
        appointmentMessage.setDeleted(c.getInt(c.getColumnIndex("deleted")) > 0);
        appointmentMessage.setRemoteId(c.getString(c.getColumnIndex("remote_id")));
        appointmentMessage.setShortUrl(c.getString(c.getColumnIndex("short_url")));
        appointmentMessage.setLastUpdated(c.getLong(c.getColumnIndex("a_last_updated")));

        return appointmentMessage;
    }

    private static ShowUpCustomerMessage getShowUpCustomerMessage(Cursor c, boolean withPrefix) {
        ShowUpCustomerMessage customerMessage = new ShowUpCustomerMessage();
        customerMessage.setId(c.getString(c.getColumnIndex(withPrefix ? "c_id" : "id")));
        customerMessage.setAddress(c.getString(c.getColumnIndex("address")));
        customerMessage.setPhoneNumber(c.getString(c.getColumnIndex("phone_number")));
        customerMessage.setEmail(c.getString(c.getColumnIndex("email")));
        customerMessage.setName(c.getString(c.getColumnIndex("name")));
        customerMessage.setDeleted(c.getInt(c.getColumnIndex("deleted")) > 0);
        customerMessage.setLastUpdated(c.getLong(c.getColumnIndex(withPrefix ? "c_last_updated" : "last_updated")));
        return customerMessage;
    }

    public static ShowUpBusinessMessage getBusiness(SharedPreferences sharedPreferences) {
        ShowUpBusinessMessage businessMessage= new ShowUpBusinessMessage();
        businessMessage.setName(sharedPreferences.getString("pref_bi_name",null));
        businessMessage.setBusinessName(sharedPreferences.getString("pref_bi_business_name",null));
        businessMessage.setPhoneNumber(sharedPreferences.getString("pref_bi_phone_number",null));
        businessMessage.setEmail(sharedPreferences.getString("pref_bi_email",null));
        businessMessage.setAddress(sharedPreferences.getString("pref_bi_address",null));
        businessMessage.setNotificationMessage(sharedPreferences.getString("pref_bi_notification_message",null));
        businessMessage.setNotificationTime(sharedPreferences.getLong("pref_bi_notification_time",0));

        return businessMessage;
    }

    public static void updateBusinessInfo(SharedPreferences settings, ShowUpBusinessMessage businessMessage) {
        SharedPreferences.Editor editor = settings.edit();
        editor.putString("pref_bi_address", businessMessage.getAddress());
        editor.putString("pref_bi_business_name", businessMessage.getBusinessName());
        editor.putString("pref_bi_email", businessMessage.getEmail());
        editor.putString("pref_bi_name", businessMessage.getName());
        editor.putString("pref_bi_notification_message", businessMessage.getNotificationMessage());
        editor.putString("pref_bi_phone_number", businessMessage.getPhoneNumber());
        editor.putLong("pref_bi_notification_time", businessMessage.getNotificationTime());
        editor.commit();
        /*Yotam we love you*/
    }

    public static ShowUpCustomerMessage getCustomer(SQLiteDatabase db, String customerId) {
        Cursor c = db.query(
                "customer",  // The table to query
                null,                               // The columns to return
                "id = ?",                                // The columns for the WHERE clause
                new String[] {customerId},                            // The values for the WHERE clause
                null,                                     // don't group the rows
                null,                                     // don't filter by row groups
                null                                 // The sort order
        );
        c.moveToFirst();
        ShowUpCustomerMessage customerMessage = getShowUpCustomerMessage(c, false);
        c.close();
        return customerMessage;
    }

    public static void deleteCustomer(SQLiteDatabase db, String customerId) {
        db.delete(
                "customer",  // The table to query
                "id = ?",
                new String[] {customerId});

    }


    public static void deleteAppointment(SQLiteDatabase db, String appointmentId) {
        db.delete(
                "appointment",
                "id = ?",
                new String[] {appointmentId});
    }

    public static ShowUpAppointmentMessage getAppointment(SQLiteDatabase db, String appointmentId) {
        Cursor c = db.rawQuery("SELECT *, a.id as a_id, a.last_updated as a_last_updated, c.id as c_id, c.last_updated as c_last_updated " +
                " FROM appointment a INNER JOIN customer c " +
                " on a.customer_id = c.id WHERE a_id = ?", new String[] {appointmentId});
        c.moveToFirst();
        ShowUpAppointmentMessage appointmentMessage = getShowUpAppointmentMessage(c);
        c.close();
        return appointmentMessage;
    }



}